/**
 * 
 */
package com.fly.entity;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * <pre>
 * 数据库工具类
 * </pre>
 * 
 * @author Christina
 * @version 1.0
 * @date 2013-11-15
 * @since JDK 1.5
 */
public class DataBaseBean
{
    private static final Logger log = LoggerFactory.getLogger(DataBaseBean.class);
    
    private static final String O_C = "C";// 新增
    
    private static final String O_R = "R";// 查询
    
    private static final String O_U = "U";// 更新
    
    private static final String O_D = "D";// 删除
    
    private static final String[] O = new String[] {O_C, O_R, O_U, O_D};
    
    private String driverClass;
    
    private String jdbcUrl;
    
    private String userName;
    
    private String password;
    
    private String optType;
    
    private String sql;
    
    private String pageSize;
    
    private String pageNumber;
    
    private Connection conn;
    
    private Statement stmt;
    
    private PreparedStatement ps;
    
    private CallableStatement cs;
    
    private ResultSet rs;
    
    private ResultSetMetaData rsmd;
    
    private DatabaseMetaData dbmd;
    
    /**
     * <pre>
     * {@link #closeConnection(boolean)}
     * </pre>
     * 
     * @throws Exception 抛出异常
     */
    private void closeConnection()
        throws Exception
    {
        closeConnection(true);
    }
    
    /**
     * <pre>
     * 关闭数据库连接
     * </pre>
     * 
     * @param commit 是否提交
     * @throws SQLException 抛出异常
     */
    private void closeConnection(boolean commit)
        throws SQLException
    {
        try
        {
            if (dbmd != null)
            {
                dbmd = null;
            }
            if (rsmd != null)
            {
                rsmd = null;
            }
            if (rs != null)
            {
                rs.close();
            }
            if (stmt != null)
            {
                stmt.close();
            }
            if (ps != null)
            {
                ps.close();
            }
            if (cs != null)
            {
                cs.close();
            }
            if (conn != null)
            {
                if (!conn.getAutoCommit())
                {
                    if (commit)
                    {
                        conn.commit();
                    }
                }
                conn.close();
            }
        }
        catch (SQLException exception)
        {
            log.error("error", exception);
            throw exception;
        }
    }
    
    /*
     * (non-Javadoc)
     * 
     * @see execute()
     */
    public Object execute()
        throws Exception
    {
        return execute(this.optType);
    }
    
    /**
     * <pre>
     * 执行数据库工具方法
     * </pre>
     * 
     * @param optType 操作类型
     * @return
     * @throws Exception 抛出异常
     */
    public Object execute(String optType)
        throws Exception
    {
        boolean accept = false;
        for (String opts : O)
        {
            if (optType.equals(opts))
            {
                accept = true;
                break;
            }
        }
        if (!accept)
        {
            throw new Exception("不可识别的操作");
        }
        if (O_C.equals(optType))
        {
            return executeCreate();
        }
        else if (O_R.equals(optType))
        {
            return executeRead();
        }
        else if (O_U.equals(optType))
        {
            return executeUpdate();
        }
        else if (O_D.equals(optType))
        {
            return executeDelete();
        }
        return null;
    }
    
    /**
     * <pre>
     * 执行数据库新增操作
     * </pre>
     * 
     * @return
     * @throws Exception
     */
    private int executeCreate()
        throws Exception
    {
        int affect;
        try
        {
            openConnection();
            affect = stmt.executeUpdate(sql);
        }
        catch (Exception exception)
        {
            log.error("error", exception);
            throw exception;
        }
        finally
        {
            closeConnection();
        }
        return affect;
    }
    
    /**
     * <pre>
     * 执行数据库删除操作
     * </pre>
     * 
     * @return
     * @throws Exception
     */
    private Object executeDelete()
        throws Exception
    {
        return executeCreate();
    }
    
    /**
     * <pre>
     * 执行查询指令
     * </pre>
     * 
     * @return
     * @throws Exception
     */
    private Object executeRead()
        throws Exception
    {
        StringBuilder sql = null;
        if (StringUtils.isNoneBlank(pageSize, pageNumber))
        {
            int pn = NumberUtils.toInt(pageNumber, 1);
            int ps = NumberUtils.toInt(pageSize, 5);
            sql = new StringBuilder();
            if ("oracle.jdbc.driver.OracleDriver".equals(driverClass))
            {
                sql.append("SELECT * FROM (")
                    .append("SELECT ROWNUM ROW_NUMBER, ori.* FROM (")
                    .append(this.sql)
                    .append(") ori WHERE ROWNUM <= ")
                    .append(ps * pn)
                    .append(") WHERE ROW_NUMBER > ")
                    .append(pn == 1 ? 0 : ps * (pn - 1));
            }
            else if ("com.mysql.jdbc.Driver".equals(driverClass))
            {
                sql.append(this.sql).append(" LIMIT ").append(ps * (pn - 1)).append(", ").append(ps);
            }
        }
        else
        {
            sql = new StringBuilder(this.sql);
        }
        log.info("执行数据库工具类,查询的SQL指令是 [\n\t{}\n]", sql);
        Map<String[], List<Object[]>> displayMap = new HashMap<>();
        try
        {
            openConnection();
            rs = stmt.executeQuery(sql.toString());
            rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            String[] title = new String[columnCount];
            for (int i = 0; i < columnCount; i++)
            {
                title[i] = rsmd.getColumnLabel(i + 1);
            }
            List<Object[]> rowList = new ArrayList<>();
            while (rs.next())
            {
                Object[] value = new Object[columnCount];
                for (int i = 0; i < columnCount; i++)
                {
                    value[i] = rs.getObject(i + 1);
                }
                rowList.add(value);
            }
            displayMap.put(title, rowList);
        }
        catch (Exception exception)
        {
            log.error("error", exception);
            throw exception;
        }
        finally
        {
            closeConnection();
        }
        return displayMap;
    }
    
    /**
     * <pre>
     * 执行数据库更新操作
     * </pre>
     * 
     * @return
     * @throws Exception
     */
    private Object executeUpdate()
        throws Exception
    {
        return executeCreate();
    }
    
    public String getPassword()
    {
        return password;
    }
    
    public String getSql()
    {
        return sql;
    }
    
    public Statement getStmt()
    {
        return stmt;
    }
    
    public String getUserName()
    {
        return userName;
    }
    
    /**
     * <pre>
     * 打开数据库连接,并初始化各数据库连接对象
     * </pre>
     * 
     * @throws Exception
     */
    private void openConnection()
        throws Exception
    {
        try
        {
            Class.forName(driverClass);
            conn = DriverManager.getConnection(jdbcUrl, userName, password);
            dbmd = conn.getMetaData();
            stmt = conn.createStatement();
        }
        catch (SQLException sqle)
        {
            log.error("error", sqle);
            throw sqle;
        }
    }
    
    /**
     * <pre>
     * 设置驱动类名称
     * </pre>
     * 
     * @param driverClass
     */
    public void setDriverClass(String driverClass)
    {
        this.driverClass = driverClass;
    }
    
    /**
     * <pre>
     * 设置数据库JDBC URL地址
     * </pre>
     * 
     * @param jdbcUrl
     */
    public void setJdbcUrl(String jdbcUrl)
    {
        this.jdbcUrl = jdbcUrl;
    }
    
    /**
     * <pre>
     * 设置操作的类型
     * C=Create=创建
     * R=Read=读取查询
     * U=Update=更新
     * D=Delete=删除
     * </pre>
     * 
     * @param optType
     */
    public void setOptType(String optType)
    {
        this.optType = optType;
    }
    
    /**
     * <pre>
     * 设置当前页码
     * 页码从1开始,不设置默认为1,仅当查询操作的时候有效
     * </pre>
     * 
     * @param pageNumber
     */
    public void setPageNumber(String pageNumber)
    {
        this.pageNumber = StringUtils.defaultIfEmpty(pageNumber, "1");
    }
    
    /**
     * <pre>
     * 设置每页显示页码
     * 仅当查询操作的时候有效
     * </pre>
     * 
     * @param pageSize
     */
    public void setPageSize(String pageSize)
    {
        this.pageSize = pageSize;
    }
    
    /**
     * <pre>
     * 设置数据库密码
     * </pre>
     * 
     * @param password
     */
    public void setPassword(String password)
    {
        this.password = password;
    }
    
    public void setSql(String sql)
    {
        this.sql = sql;
    }
    
    /**
     * <pre>
     * 设置数据库执行指令
     * </pre>
     * 
     * @param sql
     */
    public void setSQL(String sql)
    {
        this.sql = sql;
    }
    
    /**
     * <pre>
     * 设置数据库连接用户名
     * </pre>
     * 
     * @param userName
     */
    public void setUserName(String userName)
    {
        this.userName = userName;
    }
}
